/*****************************************************************************
  Copyright ?2002-2004 by Martin Cook. All rights are reserved. If you like
  this code then feel free to go ahead and use it. The only thing I ask is 
  that you don't remove or alter my copyright notice. Your use of this 
  software is entirely at your own risk. I make no claims about the 
  reliability or fitness of this code for any particular purpose. If you 
  make changes or additions to this code then please clearly mark your code 
  as yours. If you have questions or comments then please contact me at: 
  martin@codegator.com
  
  Have Fun! :o)
*****************************************************************************/


using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;

using Microsoft.ApplicationBlocks.Data.OleDb;

using CG.Security.Configuration;
using CG.Security.Data.Configuration;

namespace CG.Security.Data.Access
{
	
	/// <summary>
	/// An Access specific implementation of IRoleData.
	/// </summary>
	internal sealed class RoleData : IRoleData
	{

		// *******************************************************************
		// Attributes.
		// *******************************************************************

		#region Attributes.

		/// <summary>
		/// The SQL to create a new role.
		/// </summary>
		private static readonly string SQL_CREATE = 
			"INSERT INTO cg_security_role (role_name, role_description) " +
			"VALUES(@role_name, @role_description)";

		/// <summary>
		/// The SQL to delete a role.
		/// </summary>
		private static readonly string SQL_DELETE = 
			"DELETE FROM cg_security_role " +
			"WHERE role_id = @role_id";

		/// <summary>
		/// The SQL to update a role.
		/// </summary>
		private static readonly string SQL_UPDATE = 
			"UPDATE cg_security_role " +
			"SET role_name = @role_name, " +
			"role_description = @role_description " +
			"WHERE role_id = @role_id";

		/// <summary>
		/// The SQL to find all roles.
		/// </summary>
		private static readonly string SQL_FIND_ALL = 
			"SELECT role_id, role_name, role_description " +
			"FROM cg_security_role " +
			"ORDER BY role_name";

		/// <summary>
		/// The SQL to find a role by primary key.
		/// </summary>
		private static readonly string SQL_FIND_BY_PK = 
			"SELECT role_id, role_name, role_description " +
			"FROM cg_security_role " +
			"WHERE role_id = @role_id";

		/// <summary>
		/// Various parameter names.
		/// </summary>
		private static readonly string PARM_ROLE_ID = "@role_id";
		private static readonly string PARM_ROLE_NAME = "@role_name";
		private static readonly string PARM_ROLE_DESCRIPTION = "@role_description";
		private static readonly string PARM_TABLE_NAME = "cg_security_role";

		#endregion
		
		// ******************************************************************
		// IRoleData implementation.
		// ******************************************************************

		#region IRoleData implementation.

		/// <summary>
		/// Creates a new system-level role.
		/// </summary>
		/// <param name="roleName">The name of the role (must be unique).</param>
		/// <param name="roleDescription">The description of the role (optional).</param>
		/// <returns>The identifier for the role.</returns>
		public int Create(
			string roleName,
			string roleDescription
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_CREATE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_ROLE_NAME, OleDbType.VarChar, 50),
					new OleDbParameter(PARM_ROLE_DESCRIPTION, OleDbType.VarChar, 255)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_CREATE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = roleName;
			parms[1].Value = roleDescription;

			OleDbConnection conn = null;

			try
			{

				// Create a new connection.
				conn = new OleDbConnection(
					settings.ConnectionString
					);
                
				// Open the connection.
				conn.Open();

				// Execute the SQL statement.
				OleDbHelper.ExecuteNonQuery(
					conn,
					CommandType.Text, 
					SQL_CREATE, 
					parms
					);

				// Return the identity value.
				return IdentityHelper.GetLastValue(
					conn, 
					PARM_TABLE_NAME
					);

			} // End try

			finally
			{

				// Should we close the connection?
				if (conn != null && conn.State != ConnectionState.Closed)
					conn.Close();

			} // End finally

		} // End Create()

		// ******************************************************************

		/// <summary>
		/// Deletes the specified system-level role.
		/// </summary>
		/// <param name="roleID">The identifier for the role.</param>
		public void Delete(
			int roleID
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_DELETE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_ROLE_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_DELETE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = roleID;

			// Execute the SQL statement.
			OleDbHelper.ExecuteNonQuery(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_DELETE, 
				parms
				);

		} // End Delete()

		// ******************************************************************

		/// <summary>
		/// Updates the specified system-level role. 
		/// </summary>
		/// <param name="roleID">The identifier for the role.</param>
		/// <param name="roleName">The new name of the role (must be unique).</param>
		/// <param name="roleDescription">The new description of the role (optional).</param>
		public void Update(
			int roleID,
			string roleName,
			string roleDescription
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_UPDATE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_ROLE_NAME, OleDbType.VarChar, 50),
					new OleDbParameter(PARM_ROLE_DESCRIPTION, OleDbType.VarChar, 255),
					new OleDbParameter(PARM_ROLE_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_UPDATE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = roleName;
			parms[1].Value = roleDescription;
			parms[2].Value = roleID;

			// Execute the SQL statement.
			OleDbHelper.ExecuteNonQuery(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_UPDATE, 
				parms
				);

		} // End Update()

		// ******************************************************************

		/// <summary>
		/// Returns all the system-level roles in the database.
		/// </summary>
		/// <returns>A DataSet containing all the roles in the database.</returns>
		public System.Data.DataSet FindAll()
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Execute the SQL statement.
			return OleDbHelper.ExecuteDataset(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_FIND_ALL
				);

		} // End FindAll()

		// ******************************************************************

		/// <summary>
		/// Returns the properties for the specified system-level role.
		/// </summary>
		/// <param name="rightID">The identifier for the role.</param>
		/// <returns>A DataReader containing the properties for the specified role.</returns>
		public System.Data.IDataReader FindByPK(
			int roleID
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_FIND_BY_PK
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_ROLE_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_FIND_BY_PK, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = roleID;

			// Execute the SQL statement.
			return OleDbHelper.ExecuteReader(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_FIND_BY_PK, 
				parms
				);

		} // End FindByPK()

		#endregion 

	} // End class RoleData

} // End namespace CG.Security.Data.Access

